package com.esiag.bancs.sgbd;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

public class Database {

	private Connection db;
	private DataSource ds;
	
	public Database() {

	}
	
	public Database(DataSource ds) {
		db = ds.getConnection();
		this.ds = ds;
	}
	
	public DataSource getDataSource() {
		return ds;
	}

	public void setDataSource(DataSource ds) {
		this.ds = ds;
	}

	
	public void selectTest() {
		try {
			String query = "SELECT * FROM client";
			Statement sql = db.createStatement();
			ResultSet rs = sql.executeQuery(query);
			while (rs.next()) {
				for (int i=1; i<=9; i++) {
					System.out.print(rs.getString(i) + "\t");
				}
				System.out.println();
			}
			rs.close();
		}
		catch (Exception e) {
			System.out.println("Erreur : " + e.getMessage());
		}
	}
	
	
	public int insert(String tableName, String row[]) {
		StringBuffer req = new StringBuffer(
			"INSERT INTO " + tableName + " VALUES('" + row[0] + "'"
		);
		for (int i = 1; i < row.length; i++) {
			req.append(", '" + row[i] + "'");
		}
		req.append(")");
		try {
			Statement st = db.createStatement();
			return st.executeUpdate(req.toString());
		}
		catch (Exception e) {
			System.out.println("Erreur : " + e.getMessage());
			return -1;
		}
	}
	
	public int delete(String tableName, String key, String value) {
		try {
			String req = "DELETE FROM " + tableName + " WHERE "
			+ key + " = '" + value + "'";
			Statement st = db.createStatement();
			return st.executeUpdate(req);
		}
		catch (Exception e) {
			System.out.println("Erreur : " + e.getMessage());
			return -1;
		}
	}
	
public String [][]selectWhere(String tableName, String key, String value) {
		
		try {
			int y;
			String query = "SELECT * FROM " + tableName 
			+ " WHERE " + key + " = '" + value + "'";
			Statement sql = db.createStatement();
			ResultSet rs = sql.executeQuery(query);
			ResultSetMetaData rsm = rs.getMetaData();
			rs.last();
			int count = rs.getRow();
			rs.beforeFirst();
			
			String tc[][] = new String[count][rsm.getColumnCount()];
			
			if (rs.next()) {
				

				y = 0;
				while(!rs.isAfterLast())
				{
					tc[y][0] = rs.getString(1);
				for (int i=2; i<=rsm.getColumnCount(); i++) {
					
					tc[y][i-1] = rs.getString(i);
				}
				y++;
				rs.next();
				}
				rs.close();
				return tc;
			}
			else {
				rs.close();
				return null;
			}
			
		}
		catch (Exception e) {
			System.out.println("Erreur : " + e.getMessage());
			return null;
		}
	}
}
